package model.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import dom.domain.Examplace;
import dom.domain.ExamplaceDAO;
import dom.domain.Expert;
import dom.domain.ExpertDAO;
import dom.domain.Mobileadmistrator;
import dom.domain.MobileadmistratorDAO;
import dom.domain.Score;
import dom.domain.ScoreDAO;
import dom.domain.User;
import dom.domain.UserDAO;

public class ToExcel {
	private UserDAO userDao;
	private ExpertDAO expertDao;
	private MobileadmistratorDAO mAdminDao;
	private ExamplaceDAO epDao;
	private ScoreDAO scoreDao;
	
	public void setScoreDao(ScoreDAO scoreDao) {
		this.scoreDao = scoreDao;
	}
	private Map<String, String> map;

	public void setmAdminDao(MobileadmistratorDAO mAdminDao) {
		this.mAdminDao = mAdminDao;
	}

	public void setEpDao(ExamplaceDAO epDao) {
		this.epDao = epDao;
	}

	public void setExpertDao(ExpertDAO expertDao) {
		this.expertDao = expertDao;
	}

	public void setUserDao(UserDAO userDao) {
		this.userDao = userDao;
	}

	public InputStream ExpertToExcel(String path, String Info,String epId) {
		String[] title = Info.split("#");
		List list = expertDao.findByEpId(Integer.parseInt(epId));
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Experts");
		Object[][] value = new Object[list.size() + 1][title.length];
		for (int m = 0; m < title.length; m++) {
			value[0][m] = title[m];
		}
		System.out.println(Info);
		System.out.println(title.length);
		for (int i = 0; i < list.size(); i++) {
			Expert experts = (Expert) list.get(i);
			// System.out.println(experts.getUserId());
			ArrayList<String> arrayList = experts.getInfo(Info);
			System.out.println(arrayList.size());
			for (int j = 0; j < title.length; j++) {
				value[i + 1][j] = arrayList.get(j);
			}
		}
		ExcelUtil.writeArrayToExcel(wb, sheet, list.size() + 1, title.length,
				value);
		return getInputStream(wb, path);
	}

	public String ExcelToExpert(File file) {

		map = new HashMap<String, String>();
		Map<String, String> value = new HashMap<String, String>();
		ArrayList<String> list = new ArrayList<String>();
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(file));
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFSheet sheet = wookbook.getSheet("Experts");
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 遍历行
			for (int i = 0; i < rows; i++) {
				// 读取左上端单元格
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					// StringBuilder value = new StringBuilder();
					// StringBuilder info = new StringBuilder();
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						HSSFCell cell = row.getCell(j);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (i == 0)
									list.add(Double.toString(cell
											.getNumericCellValue()));
								else
									value.put(list.get(j), Double.toString(cell
											.getNumericCellValue()));
								break;
							case HSSFCell.CELL_TYPE_STRING:
								if (i == 0)
									list.add(cell.getStringCellValue().trim());
								else
									value.put(list.get(j), cell
											.getStringCellValue().trim());
								break;
							default:
								value.put(list.get(j), "");
								break;
							}
						}
					}
					if (i != 0) {
						for (int n = cells; n < list.size(); n++) {
							value.put(list.get(n), "");
						}
						Expert expert = new Expert();
						expert.setInfo(list, value);
						expertDao.attachDirty(expert);
					}
				}
			}
			wookbook.close();
			map.put("flag", "true");
			map.put("Info", "success");
		} catch (FileNotFoundException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			// e.printStackTrace();
		} catch (IOException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			// e.printStackTrace();
		}
		return JSONObject.fromObject(map).toString();

	}
	public String ExcelToUser(File file,String epId) {
		map = new HashMap<String, String>();
		Map<String, String> value = new HashMap<String, String>();
		ArrayList<String> list = new ArrayList<String>();
		try {
		 
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(file));
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFSheet sheet = wookbook.getSheet("Users");
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 遍历行
			for (int i = 0; i < rows; i++) {
				// 读取左上端单元格
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					// StringBuilder value = new StringBuilder();
					// StringBuilder info = new StringBuilder();
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						HSSFCell cell = row.getCell(j);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (i == 0)
									list.add(Double.toString(cell
											.getNumericCellValue()));
								else
									value.put(list.get(j), Double.toString(cell
											.getNumericCellValue()));
								break;
							case HSSFCell.CELL_TYPE_STRING:
								if (i == 0)
									list.add(cell.getStringCellValue().trim());
								else
									value.put(list.get(j), cell
											.getStringCellValue().trim());
								break;
							default:
								value.put(list.get(j), "");
								break;
							}
						}
					}
					if (i != 0) {
						for (int n = cells; n < list.size(); n++) {
							value.put(list.get(n), "");
						}
						User user = new User();
						user.setUserInfo(list, value);
						user.setEpyear(constant.constant.year);
						user.setAdmitIdAllocated(1);
//						user.setUserGeneralType("1");
						System.out.println(user.toString());
						user.setEpId(Integer.parseInt(epId));
						user.setEpAllocated(1);;
						user.setProvinceId(Integer.parseInt( user.getId().substring(0, 2)));
						userDao.attachDirty(user);
					}
				}
			}
			wookbook.close();
			map.put("flag", "true");
			map.put("Info", "success");
		} catch (FileNotFoundException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("FileNotFind" + e.getMessage());
			// e.printStackTrace();
		}catch(NumberFormatException e){
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("Integer" + e.getMessage());
		}
		catch (IOException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("IO" + e.getMessage());
		}
		return JSONObject.fromObject(map).toString();
	}
	public InputStream UserTemplate(String path){
		String[] title = Data.mapInfo.split("#");
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Users");
		Object[][] value = new Object[1][title.length];
		for (int m = 0; m < title.length; m++) {
			value[0][m] = title[m];
		}
		ExcelUtil.writeArrayToExcel(wb, sheet,1, title.length,
				value);
		return getInputStream(wb, path);
	}
//	public InputStream ClassroomToExcel(String path,String Info,int epId)
//	{
//		String[] title = Info.split("#");
//		Examplace ep = epDao.findById(epId);
//		if(ep==null){
////			map.put("flag", "false");
////			map.put("Info", "epId不存在");
//			return null;//JSONObject.fromObject(map).toString();
//		}
//		int row = ep.getEpClassNo();
//		int limitNo = ep.getLimitNo()/ep.getEpClassNo();
//		 HSSFWorkbook wb = new HSSFWorkbook();
//		 HSSFSheet sheet = wb.createSheet("Users");
//		 Object[][] value = new Object[row + 1][title.length];
//		 for (int m = 0; m < title.length; m++) {
//				value[0][m] = title[m];
//			}
//		 for (int i = 0; i < row; i++) {
//				value
//			}
//	}
	public InputStream UserToExcel(String path, String Info,int epId) {
		String[] title = Info.split("#");
		List list = userDao.findByEpId(epId);
		 HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Users");
		Object[][] value = new Object[list.size() + 1][title.length];
		for (int m = 0; m < title.length; m++) {
			value[0][m] = title[m];
		}
		for (int i = 0; i < list.size(); i++) {
			User users = (User) list.get(i);
			ArrayList<String> arrayList = users.getInfo(Info);
			for (int j = 0; j < title.length; j++) {
				value[i + 1][j] = arrayList.get(j);
			}
		}
		ExcelUtil.writeArrayToExcel(wb, sheet, list.size() + 1, title.length,
				value);

		return getInputStream(wb, path);
	}
	public InputStream UserToExcel(String path, String Info) {
		String[] title = Info.split("#");
		List list = userDao.findAll();
		 HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Users");
		Object[][] value = new Object[list.size() + 1][title.length];
		for (int m = 0; m < title.length; m++) {
			value[0][m] = title[m];
		}
		for (int i = 0; i < list.size(); i++) {
			User users = (User) list.get(i);
			ArrayList<String> arrayList = users.getInfo(Info);
			for (int j = 0; j < title.length; j++) {
				value[i + 1][j] = arrayList.get(j);
			}
		}
		ExcelUtil.writeArrayToExcel(wb, sheet, list.size() + 1, title.length,
				value);

		return getInputStream(wb, path);
	}
	public InputStream mAdminToExcel(String path, String Info) {
		String[] title = Info.split("#");
		List list = mAdminDao.findAll();
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("mAdmins");
		Object[][] value = new Object[list.size() + 1][title.length];
		for (int m = 0; m < title.length; m++) {
			value[0][m] = title[m];
		}
		for (int i = 0; i < list.size(); i++) {
			Mobileadmistrator mAdmin = (Mobileadmistrator) list.get(i);
			ArrayList<String> arrayList = mAdmin.getInfo(Info);
			for (int j = 0; j < title.length; j++) {
				value[i + 1][j] = arrayList.get(j);
			}
		}
		ExcelUtil.writeArrayToExcel(wb, sheet, list.size() + 1, title.length,
				value);
		return getInputStream(wb, path);
	}
	public InputStream mAdminToExcel(String path, String Info,String epname) {
		String[] title = Info.split("#");
		List list = mAdminDao.findByEpname(epname);
		System.out.println(epname);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("mAdmins");
		Object[][] value = new Object[list.size() + 1][title.length];
		for (int m = 0; m < title.length; m++) {
			value[0][m] = title[m];
		}
		for (int i = 0; i < list.size(); i++) {
			Mobileadmistrator mAdmin = (Mobileadmistrator) list.get(i);
			ArrayList<String> arrayList = mAdmin.getInfo(Info);
			for (int j = 0; j < title.length; j++) {
				value[i + 1][j] = arrayList.get(j);
			}
		}
		ExcelUtil.writeArrayToExcel(wb, sheet, list.size() + 1, title.length,
				value);
		return getInputStream(wb, path);
	}
	public InputStream epToExcel(String epId,String path)
	{
		Examplace ep = epDao.findById(Integer.parseInt(epId));
		if(ep==null)
			return null;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("mAdmins");
		Object[][] value = new Object[ep.getEpClassNo()+1][2];
		value[0][0] = "epClassId";
		value[0][1] = "limitNo";
		for(int i=1;i<ep.getEpClassNo()+1;i++)
		{
			value[i][0] = i;
			value[i][1] = ep.getLimitNo()/ep.getEpClassNo();
		}
		ExcelUtil.writeArrayToExcel(wb, sheet, ep.getEpClassNo()+1, 2,
				value);
		return getInputStream(wb, path);
	}
	public String ExceltoScore(File file,String fileName){
		map = new HashMap<String, String>();
		Map<String, String> value = new HashMap<String, String>();
		ArrayList<String> list = new ArrayList<String>();
		StringBuilder result = new StringBuilder();
		try {
			Workbook  wookbook ;
			if(fileName.endsWith(".xls"))
				wookbook = new HSSFWorkbook(new FileInputStream(file));
			else
				wookbook = new  XSSFWorkbook(new FileInputStream(file));
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			Sheet sheet = wookbook.getSheetAt(0);
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 遍历行
			for (int i = 0; i < rows; i++) {
				// 读取左上端单元格
				Row row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					// StringBuilder value = new StringBuilder();
					// StringBuilder info = new StringBuilder();
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						Cell cell = row.getCell(j);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (i == 0)
									list.add(Double.toString(cell
											.getNumericCellValue()));
								else
									value.put(list.get(j), Double.toString(cell
											.getNumericCellValue()));
								break;
							case HSSFCell.CELL_TYPE_STRING:
								if (i == 0)
									list.add(cell.getStringCellValue().trim());
								else
									value.put(list.get(j), cell
											.getStringCellValue().trim());
								break;
							default:
								value.put(list.get(j), "");
								break;
							}
						}
					}
					if (i != 0) {
						for (int n = cells; n < list.size(); n++) {
							value.put(list.get(n), "");
						}
						Score score = new Score();
						score.setInfo(list,value);
						Score temp = scoreDao.findById(score.getUserId());
						if(temp!=null)
						{
							result.append(score.getUserId()+"#"+"重复导入\n");
						}
						else
							scoreDao.save(score);
//						mAdminDao.attachDirty(mAdmin);
					}
				}
			}
			wookbook.close();
			map.put("flag", "true");
			map.put("Info", "success");
			map.put("result", result.toString());
		} catch (FileNotFoundException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("FileNotFind" + e.getMessage());
			// e.printStackTrace();
		} catch (IOException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("IO" + e.getMessage());

			// e.printStackTrace();
		}
		return JSONObject.fromObject(map).toString();
	}
	public String ExcelTomAdmin(File file)
	{

		map = new HashMap<String, String>();
		Map<String, String> value = new HashMap<String, String>();
		ArrayList<String> list = new ArrayList<String>();
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(file));
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFSheet sheet = wookbook.getSheet("mAdmins");
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 遍历行
			for (int i = 0; i < rows; i++) {
				// 读取左上端单元格
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					// StringBuilder value = new StringBuilder();
					// StringBuilder info = new StringBuilder();
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						HSSFCell cell = row.getCell(j);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (i == 0)
									list.add(Double.toString(cell
											.getNumericCellValue()));
								else
									value.put(list.get(j), Double.toString(cell
											.getNumericCellValue()));
								break;
							case HSSFCell.CELL_TYPE_STRING:
								if (i == 0)
									list.add(cell.getStringCellValue().trim());
								else
									value.put(list.get(j), cell
											.getStringCellValue().trim());
								break;
							default:
								value.put(list.get(j), "");
								break;
							}
						}
					}
					if (i != 0) {
						for (int n = cells; n < list.size(); n++) {
							value.put(list.get(n), "");
						}
						Mobileadmistrator mAdmin = new Mobileadmistrator();
						mAdmin.setInfo(list, value);
						mAdminDao.attachDirty(mAdmin);
					}
				}
			}
			wookbook.close();
			map.put("flag", "true");
			map.put("Info", "success");
		} catch (FileNotFoundException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("FileNotFind" + e.getMessage());
			// e.printStackTrace();
		} catch (IOException e) {
			map.put("flag", "false");
			map.put("Info", e.getMessage());
			System.out.println("IO" + e.getMessage());

			// e.printStackTrace();
		}
		return JSONObject.fromObject(map).toString();
	
	}
	public InputStream getInputStream(HSSFWorkbook wb, String path) {
		FileOutputStream fos = null;
		FileInputStream in = null;
		File f = new File(path);
		try {
			fos = new FileOutputStream(f);
			wb.write(fos);
			in = new FileInputStream(new File(path));
			fos.close();
			wb.close();
			return in;
			// ByteArrayInputStream in = new ByteArrayInputStream( fos.)
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
